﻿using Microsoft.Data.SqlClient;
using Model;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Microsoft.EntityFrameworkCore;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;
using System.Data.Common;
using Microsoft.EntityFrameworkCore.Storage;

namespace DAL
{
    public class WhOutboundDal
    {
        private readonly AppDbContext _dbContext;
        private string URL;
        public WhOutboundDal(AppDbContext dbContext)
        {
            dynamic jsonObject = JsonConvert.DeserializeObject(File.ReadAllText("appsettings.json"));
            URL = jsonObject.ConnectionStrings.SqlServer;
            _dbContext = dbContext;
        }

        /// <summary>
        /// 获取出库订单详细信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<StockOutDetailDto>> GetWhOutboundAll()
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = @"
SELECT 
    wo.stock_out_order_Id AS StockOutOrderId,
    wo.stock_out_order_no AS StockOutOrderNo,
    wo.stock_out_type AS StockOutType,
    wo.order_status AS Status,
    wo.stock_out_date AS StockOutDate,
    w.warehouse_name AS WarehouseName,
    wo.source_order_no AS SourceOrderNo,
    MAX(c.customer_name) AS CustomerName,
    MAX(c.contact_person) AS ContactPerson,
    MAX(c.contact_phone) AS ContactPhone,
    STRING_AGG(p.goods_name, ', ') WITHIN GROUP (ORDER BY p.goods_name) AS GoodsName,
    SUM(wod.out_stock_quantity) AS OutStockQuantity,
    SUM(wod.out_stock_quantity * wod.out_stock_unit_price) AS OutStockAmount,
    operator.users_true_name AS OperatorName,
    creator.users_true_name AS CreatorName,
    org.org_name AS DepartmentName,
    wo.create_time AS CreateTime,
    creato.users_true_name AS AuditorName,
    wo.auditor_time AS AuditorTime
FROM [dbo].[wh_outbound] wo
LEFT JOIN [dbo].[wh_outbound_detail] wod ON wo.stock_out_order_Id = wod.stock_out_order_Id
LEFT JOIN [dbo].[bi_warehouse] w ON wo.warehouse_id = w.warehouse_id
LEFT JOIN [dbo].[bi_product] p ON wod.goods_code = p.goods_code
LEFT JOIN [dbo].[bi_customer] c ON p.customer_id = c.customer_id
LEFT JOIN [dbo].[sys_users] operator ON wo.operator_by = operator.users_id
LEFT JOIN [dbo].[sys_users] creator ON wo.create_by = creator.users_id
LEFT JOIN [dbo].[sys_users] creato ON wo.auditor_by = creato.users_id
LEFT JOIN [dbo].[sys_organization] org ON creator.org_id = org.org_id
WHERE wo.stock_out_order_Id IS NOT NULL
GROUP BY 
    wo.stock_out_order_Id,
    wo.stock_out_order_no,
    wo.stock_out_type,
    wo.order_status,
    wo.stock_out_date,
    w.warehouse_name,
    wo.source_order_no,
    operator.users_true_name,
    creator.users_true_name,
    org.org_name,
    wo.create_time,
    creato.users_true_name,
    wo.auditor_time
ORDER BY wo.stock_out_date DESC;";

                return (await con.QueryAsync<StockOutDetailDto>(sql)).ToList();
            }
        }

        /// <summary>
        /// 获取出库订单详细信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<StockOutDetailDto>> GetWhOutboundId(long id)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = @"
                SELECT 
                    wo.stock_out_order_no AS StockOutOrderNo,
                    wo.stock_out_type AS StockOutType,
                    wo.order_status AS Status,
                    wo.stock_out_date AS StockOutDate,
                    w.warehouse_name AS WarehouseName,
                    wo.source_order_no AS SourceOrderNo,
                    c.customer_name AS CustomerName,
                    c.contact_person AS ContactPerson, 
                    c.contact_phone AS ContactPhone,
                    p.goods_name AS GoodsName,
                    wod.out_stock_quantity AS OutStockQuantity,
                    (wod.out_stock_quantity * wod.out_stock_unit_price) AS OutStockAmount,
                    operator.users_true_name AS OperatorName,
                    creator.users_true_name AS CreatorName,
                    org.org_name AS DepartmentName,
                    wo.create_time AS CreateTime
                FROM [dbo].[wh_outbound] wo
                LEFT JOIN [dbo].[wh_outbound_detail] wod ON wo.stock_out_order_Id = wod.stock_out_order_Id
                LEFT JOIN [dbo].[bi_warehouse] w ON wo.warehouse_id = w.warehouse_id
                LEFT JOIN [dbo].[bi_product] p ON wod.goods_code = p.goods_code
                LEFT JOIN [dbo].[bi_customer] c ON p.customer_id = c.customer_id
                LEFT JOIN [dbo].[sys_users] operator ON wo.operator_by = operator.users_id
                LEFT JOIN [dbo].[sys_users] creator ON wo.create_by = creator.users_id
                LEFT JOIN [dbo].[sys_organization] org ON creator.org_id = org.org_id
                WHERE wo.stock_out_order_Id IS NOT NULL and wo.[stock_out_order_Id]=@id
                ORDER BY wo.stock_out_date DESC;";
                return (await con.QueryAsync<StockOutDetailDto>(sql, new { id })).ToList();
            }
        }

        /// <summary>
        /// 根据查询条件获取出库单信息
        /// </summary>
        /// <param name="parameters">查询参数</param>
        /// <returns></returns>
        public async Task<List<StockOutDetailDto>> GetWhOutboundByParams(StockOutQueryParams parameters)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                var sql = new StringBuilder(@"
                SELECT 
                    wo.stock_out_order_no AS StockOutOrderNo,
                    wo.stock_out_type AS StockOutType,
                    wo.order_status AS Status,
                    wo.stock_out_date AS StockOutDate,
                    w.warehouse_name AS WarehouseName,
                    wo.source_order_no AS SourceOrderNo,
                    c.customer_name AS CustomerName,
                    c.contact_person AS ContactPerson, 
                    c.contact_phone AS ContactPhone,
                    p.goods_name AS GoodsName,
                    wod.out_stock_quantity AS OutStockQuantity,
                    (wod.out_stock_quantity * wod.out_stock_unit_price) AS OutStockAmount,
                    operator.users_true_name AS OperatorName,
                    creator.users_true_name AS CreatorName,
                    org.org_name AS DepartmentName,
                    wo.create_time AS CreateTime
                FROM [dbo].[wh_outbound] wo
                LEFT JOIN [dbo].[wh_outbound_detail] wod ON wo.stock_out_order_Id = wod.stock_out_order_Id
                LEFT JOIN [dbo].[bi_warehouse] w ON wo.warehouse_id = w.warehouse_id
                LEFT JOIN [dbo].[bi_product] p ON wod.goods_code = p.goods_code
                LEFT JOIN [dbo].[bi_customer] c ON p.customer_id = c.customer_id
                LEFT JOIN [dbo].[sys_users] operator ON wo.operator_by = operator.users_id
                LEFT JOIN [dbo].[sys_users] creator ON wo.create_by = creator.users_id
                LEFT JOIN [dbo].[sys_organization] org ON creator.org_id = org.org_id
                WHERE wo.stock_out_order_Id IS NOT NULL");

                // 添加查询条件
                var conditions = new List<string>();
                var parametersDict = new DynamicParameters();

                if (!string.IsNullOrEmpty(parameters.StockOutOrderNo))
                {
                    conditions.Add("wo.stock_out_order_no LIKE @StockOutOrderNo");
                    parametersDict.Add("StockOutOrderNo", $"%{parameters.StockOutOrderNo}%");
                }

                if (!string.IsNullOrEmpty(parameters.StockOutType))
                {
                    conditions.Add("wo.stock_out_type = @StockOutType");
                    parametersDict.Add("StockOutType", parameters.StockOutType);
                }

                if (parameters.Status.HasValue)
                {
                    conditions.Add("wo.order_status = @Status");
                    parametersDict.Add("Status", parameters.Status.Value);
                }

                if (parameters.StartDate.HasValue)
                {
                    conditions.Add("wo.stock_out_date >= @StartDate");
                    parametersDict.Add("StartDate", parameters.StartDate.Value);
                }

                if (parameters.EndDate.HasValue)
                {
                    conditions.Add("wo.stock_out_date <= @EndDate");
                    parametersDict.Add("EndDate", parameters.EndDate.Value);
                }

                if (parameters.WarehouseId.HasValue)
                {
                    conditions.Add("wo.warehouse_id = @WarehouseId");
                    parametersDict.Add("WarehouseId", parameters.WarehouseId.Value);
                }

                if (parameters.CustomerId.HasValue)
                {
                    conditions.Add("c.customer_id = @CustomerId");
                    parametersDict.Add("CustomerId", parameters.CustomerId.Value);
                }

                if (conditions.Count > 0)
                {
                    sql.Append(" AND " + string.Join(" AND ", conditions));
                }

                sql.Append(" ORDER BY wo.stock_out_date DESC");

                // 分页
                if (parameters.PageSize > 0)
                {
                    sql.Append(" OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY");
                    parametersDict.Add("Offset", (parameters.PageIndex - 1) * parameters.PageSize);
                    parametersDict.Add("PageSize", parameters.PageSize);
                }

                return (await con.QueryAsync<StockOutDetailDto>(sql.ToString(), parametersDict)).ToList();
            }
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="data">实体</param>
        /// <returns>结果</returns>
        public async Task<int> CreateAsync(WhOutbound data)
        {
            // 新增实体到数据库集
            _dbContext.WhOutbound.Add(data);
            // 保存更改
            return await _dbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 根据ID查询
        /// </summary>
        /// <param name="Id">ID</param>
        /// <returns>用户列表</returns>
        public async Task<WhOutbound> GetByIdAsync(long Id)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = "select * from [dbo].[wh_outbound] where [stock_out_order_Id]=@Id";
                return (await con.QueryAsync<WhOutbound>(sql,new {Id})).FirstOrDefault();
            }
        }

        /// <summary>
        /// 修改库存
        /// </summary>
        /// <param name="date">包含更新信息</param>
        public async Task<int> UpdateWhInventoryAsync(WhInventory date)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = "";
                return await con.ExecuteAsync(sql);
            }
        }
        /*
        /// <summary>
        /// 创建出库单（包含明细和库存更新）
        /// </summary>
        public async Task<int> CreateOutboundWithDetailsAsync(WhOutbound outbound, List<WhOutboundDetail> details)
        {
            using var transaction = await _dbContext.Database.BeginTransactionAsync();

            try
            {
                // 1. 保存出库主表
                _dbContext.WhOutbound.Add(outbound);
                await _dbContext.SaveChangesAsync();

                // 2. 保存出库明细并更新库存
                foreach (var detail in details)
                {
                    detail.StockOutOrderId = outbound.StockOutOrderId;
                    _dbContext.WhOutboundDetail.Add(detail);

                    // 更新库存（无批次逻辑）
                    await UpdateInventoryForOutbound(detail, outbound.WarehouseId);
                }

                // 3. 如果是销售出库，更新销售订单的出库数量
                if (!string.IsNullOrEmpty(outbound.SourceOrderNo))
                {
                    await UpdateSalesOrderOutboundQty(outbound.SourceOrderNo, details);
                }

                await _dbContext.SaveChangesAsync();
                await transaction.CommitAsync();

                return 1;
            }
            catch (Exception ex)
            {
                await transaction.RollbackAsync();
                throw new Exception($"创建出库单失败: {ex.Message}");
            }
        }

        */
        /// <summary>
        /// 更新库存
        /// </summary>
        private async Task UpdateInventoryForOutbound(WhOutboundDetail detail, long warehouseId)
        {
            using var con = new SqlConnection(URL);

            // 1. 根据货品编号查询货品ID
            var getGoodsIdSql = @"
        SELECT goods_id 
        FROM bi_product 
        WHERE goods_code = @GoodsCode AND is_delete = '0'";

            var goodsId = await con.ExecuteScalarAsync<long?>(getGoodsIdSql, new { detail.GoodsCode });

            if (goodsId == null)
            {
                throw new Exception($"未找到货品编号为 {detail.GoodsCode} 的货品");
            }

            // 2. 根据仓库ID和货品ID找到库存记录（无批次条件）
            var getInventorySql = @"
        SELECT 
            inventory_id,
            available_inventory_qty,
            total_planned_quantity
        FROM wh_inventory 
        WHERE warehouse_id = @WarehouseId 
          AND goods_id = @GoodsId";

            var inventory = await con.QueryFirstOrDefaultAsync<WhInventory>(getInventorySql, new
            {
                WarehouseId = warehouseId,
                GoodsId = goodsId
            });

            if (inventory == null)
            {
                throw new Exception($"未找到货品 {detail.GoodsCode} 在仓库 {warehouseId} 下的库存记录");
            }

            if (inventory.AvailableInventoryQty < detail.OutStockQuantity)
            {
                throw new Exception($"库存不足，可用库存：{inventory.AvailableInventoryQty}，出库数量：{detail.OutStockQuantity}");
            }

            // 3. 更新库存记录
            var updateInventorySql = @"
        UPDATE wh_inventory 
        SET available_inventory_qty = available_inventory_qty - @OutStockQuantity,
            total_planned_quantity = total_planned_quantity + @OutStockQuantity,
            update_time = GETDATE()
        WHERE inventory_id = @InventoryId";

            await con.ExecuteAsync(updateInventorySql, new
            {
                OutStockQuantity = detail.OutStockQuantity,
                InventoryId = inventory.InventoryId
            });

            // 4. 如果有选择库位，更新库位明细
            if (detail.StorageLocationId > 0)
            {
                await UpdateLocationDetail(detail, warehouseId, goodsId.Value);
            }
        }

        /// <summary>
        /// 更新库位明细
        /// </summary>
        private async Task UpdateLocationDetail(WhOutboundDetail detail, long warehouseId, long goodsId)
        {
            using var con = new SqlConnection(URL);

            // 检查库位明细是否存在
            var checkLocationDetailSql = @"
        SELECT wl_detail_id, available_invenroty_quantity
        FROM wh_location_detail 
        WHERE storage_location_id = @StorageLocationId 
          AND goods_id = @GoodsId";

            var locationDetail = await con.QueryFirstOrDefaultAsync(checkLocationDetailSql, new
            {
                StorageLocationId = detail.StorageLocationId,
                GoodsId = goodsId
            });

            if (locationDetail != null)
            {
                // 更新现有记录
                var updateLocationDetailSql = @"
            UPDATE wh_location_detail 
            SET available_invenroty_quantity = available_invenroty_quantity - @OutStockQuantity,
                update_time = GETDATE()
            WHERE wl_detail_id = @WlDetailId";

                await con.ExecuteAsync(updateLocationDetailSql, new
                {
                    OutStockQuantity = detail.OutStockQuantity,
                    WlDetailId = locationDetail.wl_detail_id
                });
            }
            else
            {
                // 插入新记录（虽然理论上不应该出现这种情况）
                var insertLocationDetailSql = @"
            INSERT INTO wh_location_detail 
            (storage_location_id, goods_id, batch_id, available_invenroty_quantity, 
             inbound_total, total_planned_quantity, create_time, update_time)
            VALUES 
            (@StorageLocationId, @GoodsId, 0, -@OutStockQuantity, 
             0, @OutStockQuantity, GETDATE(), GETDATE())";

                await con.ExecuteAsync(insertLocationDetailSql, new
                {
                    StorageLocationId = detail.StorageLocationId,
                    GoodsId = goodsId,
                    OutStockQuantity = detail.OutStockQuantity
                });
            }
        }

        /// <summary>
        /// 更新销售订单出库数量
        /// </summary>
        private async Task UpdateSalesOrderOutboundQty(string sourceOrderNo, List<WhOutboundDetail> details)
        {
            if (string.IsNullOrEmpty(sourceOrderNo)) return;

            using var con = new SqlConnection(URL);

            foreach (var detail in details)
            {
                var sql = @"
                    UPDATE om_sales_order_detail 
                    SET outbound_qty = outbound_qty + @OutboundQty,
                        unshipped_qty = unshipped_qty - @OutboundQty
                    WHERE sales_order_id = (SELECT sales_order_id FROM om_sales_order WHERE sales_order_no = @SourceOrderNo)
                    AND goods_code = @GoodsCode";

                await con.ExecuteAsync(sql, new
                {
                    OutboundQty = detail.OutStockQuantity,
                    SourceOrderNo = sourceOrderNo,
                    GoodsCode = detail.GoodsCode
                });
            }
        }


        /// <summary>
        /// 生成出库单号
        /// </summary>
        private async Task<string> GenerateOutboundOrderNo()
        {
            var date = DateTime.Now.ToString("yyyyMMdd");
            var count = await _dbContext.WhOutbound
                .CountAsync(o => o.CreateTime.Date == DateTime.Today);

            return $"OUT{date}{(count + 1).ToString().PadLeft(4, '0')}";
        }

        /// <summary>
        /// 根据销售订单获取可出库货品
        /// </summary>
        public async Task<List<SalesOrderAvailableGoodsDto>> GetAvailableGoodsBySalesOrder(string salesOrderNo)
        {
            using var con = new SqlConnection(URL);

            var sql = @"
                SELECT 
                    p.goods_id AS GoodsId,
                    sod.goods_code AS GoodsCode,
                    p.goods_name AS GoodsName,
                    pt.goods_type_name as GoodsTypeName,
                    p.specification AS Specification,
                    ISNULL(wi.available_inventory_qty, 0) AS AvailableStock,
                    wh.warehouse_name as WarehouseName,
                    lio.storage_location_name as StorageLocationName,
                    wi.inventory_id as InventoryId,
                    wi.warehouse_id as WarehouseId,
                    lio.storage_location_id as StorageLocationId,
                    sod.order_quantity as SalesQuantity,
                    sod.outbound_qty as OutboundQuantity,
                    sod.unshipped_qty as NonOutboundQuantity,
                    sod.sales_unit_price as OutboundUnitPrice
                FROM om_sales_order_detail sod
                INNER JOIN om_sales_order so ON sod.sales_order_id = so.sales_order_id
                INNER JOIN bi_product p ON sod.goods_code = p.goods_code
                INNER JOIN [dbo].[bi_product_category] pt ON p.goods_type_id = pt.goods_type_id
                LEFT JOIN wh_inventory wi ON p.goods_id = wi.goods_id
                LEFT JOIN [dbo].[wh_location] lio ON lio.storage_location_id = wi.storage_location_id
                LEFT JOIN [dbo].[bi_warehouse] wh ON wh.warehouse_id = wi.warehouse_id
                WHERE so.sales_order_no = @SalesOrderNo
                    AND sod.unshipped_qty > 0
                    AND p.is_delete = '0'
                ORDER BY p.goods_name";

            return (await con.QueryAsync<SalesOrderAvailableGoodsDto>(sql, new { SalesOrderNo = salesOrderNo })).ToList();
        }

        /// <summary>
        /// 获取货品的库存信息
        /// </summary>
        public async Task<List<GoodsInventoryInfoDto>> GetGoodsInventoryInfo(string goodsCode, long warehouseId)
        {
            using var con = new SqlConnection(URL);

            var sql = @"
            SELECT 
                wi.inventory_id AS InventoryId,
                wi.available_inventory_qty AS AvailableQuantity,
                wi.unit_price AS UnitPrice,
                p.goods_id AS GoodsId
            FROM wh_inventory wi
            INNER JOIN bi_product p ON wi.goods_id = p.goods_id
            WHERE p.goods_code = @GoodsCode 
                AND wi.WarehouseId = @WarehouseId
                AND wi.available_inventory_qty > 0
            ORDER BY wi.available_inventory_qty DESC";

            return (await con.QueryAsync<GoodsInventoryInfoDto>(sql, new
            {
                GoodsCode = goodsCode,
                WarehouseId = warehouseId
            })).ToList();
        }
        /// <summary>
        /// 获取库位
        /// </summary>
        /// <returns></returns>
        public async Task<List<WhLocation>> WhLocationAllAsync()
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = "select * from [dbo].[wh_location]";
                return (await con.QueryAsync<WhLocation>(sql)).ToList();
            }
        }
        /// <summary>
        /// 根据仓库ID获取库位
        /// </summary>
        /// <param name="warehouseId">仓库ID</param>
        /// <returns></returns>
        public async Task<List<WhLocation>> GetLocationsByWarehouseIdAsync(long warehouseId)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = "select * from [dbo].[wh_location] where [warehouse_id]=@warehouseId";
                return (await con.QueryAsync<WhLocation>(sql, new { warehouseId })).ToList();
            }
        }
        public async Task<List<Users>> UserAll()
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = "select [users_id],[users_name],[users_hone_umber] from [dbo].[sys_users]";
                return (await con.QueryAsync<Users>(sql)).ToList();
            }
        }

        /// <summary>
        /// 审核
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        public async Task<int> UpdateAuditorComment(Auditor date)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = @"UPDATE [dbo].[wh_outbound] SET
                    [auditor_by]=@AuditorBy,[auditor_time]=GETDATE(),
                    [auditor_comment]=@AuditorComment,[order_status]=@OrderStatus 
                WHERE [stock_out_order_Id]=@Id";
                return await con.ExecuteAsync(sql, date);
            }
        }
        /// <summary>
        /// 状态更改
        /// </summary>
        /// <returns></returns>
        public async Task<int> AuditorStatus(string status, long id)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = "UPDATE [dbo].[wh_outbound] SET [order_status]=@status WHERE [stock_out_order_Id]=@id";
                return await con.ExecuteAsync(sql, new { status, id });
            }
        }
        /// <summary>
        /// 状态更改(启用停用)
        /// </summary>
        /// <returns></returns>
        public async Task<int> Status(string status, long id)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = "UPDATE [dbo].[wh_outbound] SET [is_delete]=@status WHERE [stock_out_order_Id]=@id";
                return await con.ExecuteAsync(sql, new { status, id });
            }
        }
        /// <summary>
        /// 变更销售订单明细的已出库数量
        /// </summary>
        /// <param name="code"></param>
        /// <param name=""></param>
        /// <returns></returns>
        public async Task<int> SalesOrderInventory(string code, decimal sl)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = "";
                long id = (con.Query<OmSalesOrder>(sql, new { code }).FirstOrDefault()).SalesOrderId;
                string sql1 = "select [sales_order_id] from [dbo].[om_sales_order]=@code";
                return (await con.ExecuteAsync(sql1));
            }
        }
        /*
        /// <summary>
        /// 创建出库单（只更新计划出库数量，不实际减少库存）
        /// </summary>
        public async Task<int> CreateOutboundWithDetailsAsync(WhOutbound outbound, List<WhOutboundDetail> details)
        {
            using var transaction = await _dbContext.Database.BeginTransactionAsync();

            try
            {
                // 保存出库主表
                _dbContext.WhOutbound.Add(outbound);
                await _dbContext.SaveChangesAsync();

                // 保存出库明细并更新计划出库数量
                foreach (var detail in details)
                {
                    detail.StockOutOrderId = outbound.StockOutOrderId;
                    _dbContext.WhOutboundDetail.Add(detail);

                    // 更新计划出库数量
                    await UpdatePlannedOutboundQuantity(detail, outbound.WarehouseId);
                }

                await _dbContext.SaveChangesAsync();
                await transaction.CommitAsync();

                return 1;
            }
            catch (Exception ex)
            {
                await transaction.RollbackAsync();
                throw new Exception($"创建出库单失败: {ex.Message}");
            }
        }
        */

        /// <summary>
        /// 创建出库单（只更新计划出库数量，不实际减少库存）- 使用 Dapper 和 SQL
        /// </summary>
        public async Task<int> CreateOutboundWithDetailsAsync(WhOutbound outbound, List<WhOutboundDetail> details)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                try
                {
                    // 1. 插入出库主表
                    var insertOutboundSql = @"
                INSERT INTO [dbo].[wh_outbound] 
                (
                    stock_out_order_no, warehouse_id, source_order_no, stock_out_type, 
                    stock_out_date, out_warehouse_id, operator_by, operator_date, 
                    order_status, auditor_by, auditor_time, auditor_comment, 
                    create_by, create_time, update_by, update_time
                )
                OUTPUT INSERTED.stock_out_order_Id
                VALUES 
                (
                    @StockOutOrderNo, @WarehouseId, @SourceOrderNo, @StockOutType,
                    GETDATE(), @WarehouseId, @OperatorBy, GETDATE(),
                    @OrderStatus, @AuditorBy, GETDATE(), @AuditorComment,
                    @CreateBy, GETDATE(), @UpdateBy, GETDATE()
                )";

                    // 执行插入并获取出库单ID
                    var stockOutOrderId = await con.ExecuteScalarAsync<long>(insertOutboundSql, outbound);
                    Console.WriteLine($"出库主表插入成功，ID: {stockOutOrderId}");

                    // 2. 插入出库明细
                    var insertDetailSql = @"
                INSERT INTO [dbo].[wh_outbound_detail] 
                (
                    stock_out_order_Id, goods_code, product_unit, 
                    storage_location_id, out_stock_unit_price, out_stock_quantity, 
                    production_date, is_delete, create_by, create_time, 
                    update_by, update_time, attachment_id, remarks
                )
                VALUES 
                (
                    @StockOutOrderId, @GoodsCode, @ProductUnit,
                    @StorageLocationId, @OutStockUnitPrice, @OutStockQuantity,
                     GETDATE(), @IsDelete, @CreateBy, GETDATE(),
                    @UpdateBy, GETDATE(), @AttachmentId, @Remarks
                )";

                    foreach (var detail in details)
                    {
                        detail.StockOutOrderId = stockOutOrderId;
                        detail.IsDelete = "0";
                        detail.BatchNo = ""; // 清空批次号

                        await con.ExecuteAsync(insertDetailSql, detail);

                        // 使用明细中的仓库ID和库位ID来更新计划出库数量
                        await UpdatePlannedOutboundQuantity(con, detail, outbound.WarehouseId, detail.StorageLocationId);
                    }

                    return 1;
                }
                catch (Exception ex)
                {
                    if (ex.InnerException != null)
                    {
                        Console.WriteLine($"内部异常: {ex.InnerException.Message}");
                    }
                    throw new Exception($"创建出库单失败: {ex.Message}", ex);
                }
            }
        }
        /// <summary>
        /// 更新计划出库数量（审核前）- 使用 Dapper
        /// </summary>
        private async Task UpdatePlannedOutboundQuantity(SqlConnection con, WhOutboundDetail detail, long warehouseId, long storageLocationId)
        {
            try
            {
                // 1. 根据货品编号查询货品ID
                var getGoodsIdSql = @"
            SELECT goods_id 
            FROM bi_product 
            WHERE goods_code = @GoodsCode AND is_delete = '0'";

                var goodsId = await con.ExecuteScalarAsync<long?>(getGoodsIdSql, new { detail.GoodsCode });

                if (goodsId == null)
                {
                    throw new Exception($"未找到货品编号为 {detail.GoodsCode} 的货品");
                }

                // 2. 根据仓库ID、库位ID和货品ID找到库存记录
                var getInventorySql = @"
            SELECT 
                inventory_id,
                available_inventory_qty,
                total_planned_quantity
            FROM wh_inventory 
            WHERE warehouse_id = @WarehouseId 
              AND storage_location_id = @StorageLocationId
              AND goods_id = @GoodsId";

                var inventory = await con.QueryFirstOrDefaultAsync<WhInventory>(getInventorySql, new
                {
                    WarehouseId = warehouseId,
                    StorageLocationId = storageLocationId,
                    GoodsId = goodsId
                });

                if (inventory == null)
                {
                    throw new Exception($"未找到货品 {detail.GoodsCode} 在仓库 {warehouseId} 库位 {storageLocationId} 下的库存记录");
                }

                // 3. 验证库存是否足够
                var totalAvailable = inventory.AvailableInventoryQty + inventory.TotalPlannedQuantity;
                if (totalAvailable < detail.OutStockQuantity)
                {
                    throw new Exception($"库存不足，总可用量：{totalAvailable}，计划出库数量：{detail.OutStockQuantity}");
                }

                // 4. 更新计划出库数量（不减少可用库存）
                var updateInventorySql = @"
            UPDATE wh_inventory 
            SET total_planned_quantity = total_planned_quantity + @OutStockQuantity
            WHERE inventory_id = @InventoryId";

                var result = await con.ExecuteAsync(updateInventorySql, new
                {
                    OutStockQuantity = detail.OutStockQuantity,
                    InventoryId = inventory.InventoryId
                });

                Console.WriteLine($"计划出库数量更新成功，影响行数: {result}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"更新计划出库数量失败: {ex.Message}");
                throw;
            }
        }
        /// <summary>
        /// 更新计划出库数量（审核前）
        /// </summary>
        private async Task UpdatePlannedOutboundQuantity(WhOutboundDetail detail, long warehouseId)
        {
            using var con = new SqlConnection(URL);

            // 1. 根据货品编号查询货品ID
            var getGoodsIdSql = @"
        SELECT goods_id 
        FROM bi_product 
        WHERE goods_code = @GoodsCode AND is_delete = '0'";

            var goodsId = await con.ExecuteScalarAsync<long?>(getGoodsIdSql, new { detail.GoodsCode });

            if (goodsId == null)
            {
                throw new Exception($"未找到货品编号为 {detail.GoodsCode} 的货品");
            }

            // 2. 根据仓库ID和货品ID找到库存记录
            var getInventorySql = @"
        SELECT 
            inventory_id,
            available_inventory_qty,
            total_planned_quantity
        FROM wh_inventory 
        WHERE warehouse_id = @WarehouseId 
          AND goods_id = @GoodsId";

            var inventory = await con.QueryFirstOrDefaultAsync<WhInventory>(getInventorySql, new
            {
                WarehouseId = warehouseId,
                GoodsId = goodsId
            });

            if (inventory == null)
            {
                throw new Exception($"未找到货品 {detail.GoodsCode} 在仓库 {warehouseId} 下的库存记录");
            }

            // 3. 验证库存是否足够（可用库存 + 已计划出库数量 >= 本次计划出库数量）
            var totalAvailable = inventory.AvailableInventoryQty + inventory.TotalPlannedQuantity;
            if (totalAvailable < detail.OutStockQuantity)
            {
                throw new Exception($"库存不足，总可用量：{totalAvailable}，计划出库数量：{detail.OutStockQuantity}");
            }

            // 4. 更新计划出库数量（不减少可用库存）
            var updateInventorySql = @"
                UPDATE wh_inventory 
                SET total_planned_quantity = total_planned_quantity + @OutStockQuantity,
                    update_time = GETDATE()
                WHERE inventory_id = @InventoryId";

            await con.ExecuteAsync(updateInventorySql, new
            {
                OutStockQuantity = detail.OutStockQuantity,
                InventoryId = inventory.InventoryId
            });
        }

        /// <summary>
        /// 审核通过后执行实际出库操作
        /// </summary>
        public async Task<int> ExecuteActualOutbound(long stockOutOrderId)
        {
            

            try
            {
                // 1. 获取出库单信息
                var outbound = await GetByIdAsync(stockOutOrderId);
                if (outbound == null)
                {
                    throw new Exception($"未找到出库单：{stockOutOrderId}");
                }

                // 2. 获取出库明细
                var details = await GetOutboundDetailsByOrderId(stockOutOrderId);

                // 3. 执行实际出库操作
                foreach (var detail in details)
                {
                    await ExecuteActualOutboundForDetail(detail, outbound.WarehouseId);
                }

                // 4. 如果是销售出库，更新销售订单状态
                if (!string.IsNullOrEmpty(outbound.SourceOrderNo))
                {
                    await UpdateSalesOrderStatus(outbound.SourceOrderNo);
                }

                
                return 1;
            }
            catch (Exception ex)
            {
                
                throw new Exception($"执行实际出库失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 执行单条明细的实际出库
        /// </summary>
        private async Task ExecuteActualOutboundForDetail(WhOutboundDetail detail, long warehouseId)
        {
            using var con = new SqlConnection(URL);

            // 1. 根据货品编号查询货品ID
            var getGoodsIdSql = @"
        SELECT goods_id 
        FROM bi_product 
        WHERE goods_code = @GoodsCode AND is_delete = '0'";

            var goodsId = await con.ExecuteScalarAsync<long?>(getGoodsIdSql, new { detail.GoodsCode });

            if (goodsId == null) return;

            // 2. 获取库存信息（根据仓库和库位）
            var getInventorySql = @"
        SELECT 
            inventory_id,
            available_inventory_qty,
            total_planned_quantity
        FROM wh_inventory 
        WHERE warehouse_id = @WarehouseId 
          AND storage_location_id = @StorageLocationId
          AND goods_id = @GoodsId";

            var inventory = await con.QueryFirstOrDefaultAsync<WhInventory>(getInventorySql, new
            {
                WarehouseId = warehouseId,
                StorageLocationId = detail.StorageLocationId,
                GoodsId = goodsId
            });

            if (inventory == null) return;

            // 3. 验证计划数量是否足够
            if (inventory.TotalPlannedQuantity < detail.OutStockQuantity)
            {
                throw new Exception($"计划出库数量不足，计划数量：{inventory.TotalPlannedQuantity}，出库数量：{detail.OutStockQuantity}");
            }

            // 4. 执行实际出库：减少可用库存和计划数量
            var updateInventorySql = @"
        UPDATE wh_inventory 
        SET available_inventory_qty = available_inventory_qty - @OutStockQuantity,
            total_planned_quantity = total_planned_quantity - @OutStockQuantity
        WHERE inventory_id = @InventoryId";

            await con.ExecuteAsync(updateInventorySql, new
            {
                OutStockQuantity = detail.OutStockQuantity,
                InventoryId = inventory.InventoryId
            });

            // 5. 更新库位明细
            await UpdateLocationDetailForOutbound(detail, warehouseId, goodsId.Value);
        }

        /// <summary>
        /// 获取出库单明细
        /// </summary>
        private async Task<List<WhOutboundDetail>> GetOutboundDetailsByOrderId(long stockOutOrderId)
        {
            using var con = new SqlConnection(URL);

            string sql = @" SELECT *  FROM wh_outbound_detail WHERE stock_out_order_Id = @StockOutOrderId AND is_delete = '0'";
            return (await con.QueryAsync<WhOutboundDetail>(sql, new { StockOutOrderId = stockOutOrderId })).ToList();
        }

        /// <summary>
        /// 更新销售订单状态
        /// </summary>
        private async Task UpdateSalesOrderStatus(string salesOrderNo)
        {
            using var con = new SqlConnection(URL);

            // 1. 更新销售订单明细的出库数量
            var updateDetailSql = @"
        UPDATE sod
        SET sod.outbound_qty = sod.outbound_qty + wod.out_stock_quantity,
            sod.unshipped_qty = sod.unshipped_qty - wod.out_stock_quantity
        FROM om_sales_order_detail sod
        INNER JOIN wh_outbound_detail wod ON sod.goods_code = wod.goods_code
        INNER JOIN wh_outbound wo ON wod.stock_out_order_Id = wo.stock_out_order_Id
        WHERE wo.source_order_no = @SalesOrderNo 
          AND wo.order_status = '3'  -- 只处理已审核的出库单
          AND wod.is_delete = '0'";

            await con.ExecuteAsync(updateDetailSql, new { SalesOrderNo = salesOrderNo });

            // 2. 检查并更新销售订单状态
            var checkOrderStatusSql = @"
        -- 获取订单的总体出库情况
        SELECT 
            so.sales_order_id,
            SUM(sod.order_quantity) as total_order_qty,
            SUM(sod.outbound_qty) as total_outbound_qty
        FROM om_sales_order so
        INNER JOIN om_sales_order_detail sod ON so.sales_order_id = sod.sales_order_id
        WHERE so.sales_order_no = @SalesOrderNo
        GROUP BY so.sales_order_id";

            var statusResult = await con.QueryFirstOrDefaultAsync<(long SalesOrderId, decimal TotalOrderQty, decimal TotalOutboundQty)>(
                checkOrderStatusSql, new { SalesOrderNo = salesOrderNo });

            string newOrderStatus = "3"; // 默认已审核

            if (statusResult.TotalOutboundQty >= statusResult.TotalOrderQty)
            {
                newOrderStatus = "5"; // 全部出库完成
            }
            else if (statusResult.TotalOutboundQty > 0)
            {
                newOrderStatus = "4"; // 部分出库（需要在前端字典中添加此状态）
            }

            // 3. 更新销售订单状态
            var updateOrderStatusSql = @"
        UPDATE om_sales_order 
        SET order_status = @OrderStatus,
            update_time = GETDATE()
        WHERE sales_order_no = @SalesOrderNo";

            await con.ExecuteAsync(updateOrderStatusSql, new
            {
                OrderStatus = newOrderStatus,
                SalesOrderNo = salesOrderNo
            });
        }

        /// <summary>
        /// 更新库位明细（实际出库时）
        /// </summary>
        private async Task UpdateLocationDetailForOutbound(WhOutboundDetail detail, long warehouseId, long goodsId)
        {
            using var con = new SqlConnection(URL);

            // 检查库位明细是否存在
            var checkLocationDetailSql = @"
        SELECT wl_detail_id, available_invenroty_quantity
        FROM wh_location_detail 
        WHERE storage_location_id = @StorageLocationId 
          AND goods_id = @GoodsId";

            var locationDetail = await con.QueryFirstOrDefaultAsync(checkLocationDetailSql, new
            {
                StorageLocationId = detail.StorageLocationId,
                GoodsId = goodsId
            });

            if (locationDetail != null)
            {
                // 更新现有记录
                var updateLocationDetailSql = @"
            UPDATE wh_location_detail 
            SET available_invenroty_quantity = available_invenroty_quantity - @OutStockQuantity,
                update_time = GETDATE()
            WHERE wl_detail_id = @WlDetailId";

                await con.ExecuteAsync(updateLocationDetailSql, new
                {
                    OutStockQuantity = detail.OutStockQuantity,
                    WlDetailId = locationDetail.wl_detail_id
                });
            }
            // 如果库位明细不存在，在实际出库时不需要创建，因为库存已经减少
        }
        /// <summary>
        /// 根据ID删除订单及明细
        /// </summary>
        /// <param name="Id">订单ID</param>
        public async Task<int> DeleteAsync(long Id)
        {
            using var transaction = await _dbContext.Database.BeginTransactionAsync();

            try
            {
                // 先执行明细删除的 SQL
                var detailDeleteSql = $"DELETE FROM [dbo].[wh_outbound_detail] WHERE  [stock_out_detail_id]= {Id}";
                await _dbContext.Database.ExecuteSqlRawAsync(detailDeleteSql);

                // 再执行订单删除的 SQL
                var orderDeleteSql = $"DELETE FROM [dbo].[wh_outbound]WHERE [stock_out_order_Id]= {Id}";
                var result = await _dbContext.Database.ExecuteSqlRawAsync(orderDeleteSql);

                await transaction.CommitAsync();
                return result;
            }
            catch (Exception ex)
            {
                await transaction.RollbackAsync();
                Console.WriteLine($"删除订单时发生错误: {ex.Message}");
                throw;
            }
        }
    }
}